CREATE        PROCEDURE [dbo].[amsp_GetTaggedPage]
    @TPLID NUMERIC,
    @LstInterestCategoryID VARCHAR(1000),
    @TPPID NUMERIC,
    @HideMemberOnlyFlag NUMERIC,
    @ExtraURLVar VARCHAR(1000),
    @ContactID NUMERIC = 0,
    @TPOperator CHAR(1) = 'A'
AS
SET NOCOUNT ON
  /*
  ** DESCRIPTION:
  ** ------------
  ** Generate queries for a tagged page layout.  This stored procedure will perform the logic
  ** to follow most of the customization specified via the user interface.  It will filter out
  ** contents by the given interest categories, figures out maximum rows to return, sorting
  ** logic, etc.  
  **   
  ** Then the queries returned will be used by the portlets (components) to generate the output
  **
  ** INPUTS:
  ** -------
  ** @TPLID -> TaggedPageLayoutID - Identifies which tagged page has been called.
  ** @LstInterestCategoryID -> List of additional interest categories (vs ones specified in the tagged page)
  ** @TPPID -> TaggedPagePortletID - (Optional) Identifies "view all" mode.  Therefore, only the
  **           query for this portlet will be generated.
  ** @HideMemberOnlyFlag -> (Optional) Indicates Members only content/record/etc should not be returned (CM).
  ** @ExtraURLVar -> (Optional) Any extra URL variables that may be used by this script.
  ** @ContactID -> User's ContactID.  Currently, only used to retrieve user interest categories
  **               when @TPOperator = "U"
  ** @TPOperator -> An additional variable for indicating special attention.
  **
  ** RESULT SET:
  ** -----------
  ** Query sets for each TaggedPagePorletID (components specified in the tagged page).
  **
  ** NOTES:
  ** ------
  **
  ** PROGRAMMING CONSIDERATIONS:
  ** ---------------------------
  ** Dynamic SQL is only be used when absolutely necessary.
  **
  ** HISTORY:
  ** --------
  ** 01/17/2002   Intae Kim   Initial Version Created
  ** 06/20/2002   Intae Kim   Handles two new input variables, @ContactID and @TPOperator
  ** 10/22/2002   Intae Kim   Added Forum and Survey.  Also allows ability to accomodate more than
  **                          one SQL per component.
  ** 01/21/2003   Intae Kim   Added a feature for handling date range and border info.
  ** 07/08/2003   Intae Kim   Added ability to use a date column specified by the user.
  ** 08/18/2003   Intae Kim   Handles "Hide" feature for CM.
  ** 09/17/2003   Intae Kim   Corrected handling of different "Name" coloums in same query for CM
  ** 10/24/2003   Intae Kim   When no @LstInterestCategoryID is passed in and there are heavy amount
  **                          of data in the system, inserting "all" records into a temp table slowed
  **                          the SP down.  Adding further checking to limit valid data enhanced performance.
  ** 11/04/2003   Intae Kim   Added ability to handle range instead of just date range
  ** 12/15/2003   E.Tatsui    Added a fix for ECM. It was failing when tables from other products don't exist.
  ** 03/22/2004   E.Tatsui    Removed where clause that filters out items by end date for conference and calendar.
  ** 09/27/2005   E.Tatsui    Added Perfomance changes by Dmitry. Some of the queries replaced with
                  Views and improved dynamic SQL. Added optmization hints to the queries.
*/

BEGIN
  DECLARE
  @SQL                         nVARCHAR(4000),
  @TempVar                     VARCHAR(100),
  @TempList                    VARCHAR(1000),
  @StringPtr                   INT,
  @LastStringPtr               INT,
  @TaggedSectionID             NUMERIC,
  @TaggedPagePortletID         NUMERIC,
  @ComponentCode               CHAR(2),
  @MaxRows                     NUMERIC,
  @PrimarySort                 VARCHAR(50),
  @PrimarySortAscDescInd       CHAR(1),
  @SecondarySort               VARCHAR(50),
  @SecondarySortAscDescInd     CHAR(1),
  @OrderNum                    NUMERIC,
  @SQLNumber                   NUMERIC,
  @RangeColumn                 VARCHAR(50),
  @ColumnType                  VARCHAR(10),
  @RangeFrom                   VARCHAR(25),
  @RangeTo                     VARCHAR(25),
  @TempStr1                    VARCHAR(1000),
  @TempStr2                    VARCHAR(1000),
  @UserDefinedDate1            VARCHAR(50),
  @CurrentDate                 VARCHAR(10),
  @EcmFlag                     bit



DECLARE @DEBUG Datetime
DECLARE @tmpSQL nvarchar(2000)
DECLARE @SQLString nvarchar(4000)
  -- Figure out this database is for eCM.
  IF OBJECT_ID('Press_Release') IS NULL
    SET @EcmFlag = 1
  ELSE
    SET @EcmFlag = 0

  -- Tag page contains one or more "sections" of page, such as top and bottom or
  -- left and right.  Each section can contain one or more "portlets" which handles
  -- the display.  

  -- Grab all portlet information for every sections in this tagged page and put it into #TempTPP.
  -- This table will eventually contain records that have been filtered by the interest categories.

SELECT     TPS.TaggedSectionID, TPP.TaggedPagePortletID, TT.ComponentCode, TT.SQLNumber, TPP.MaxRows, TPP.PrimarySort, TPP.PrimarySortAscDescInd,
                      TPP.SecondarySort, TPP.SecondarySortAscDescInd, TPP.OrderNum, TPP.UseOR, TPP.RangeFrom, TPP.RangeTo, TPP.RangeColumn,
                      TPCC.ColumnType
INTO         [#TempTPP]
FROM         Tagged_Page_Section AS TPS WITH (UPDLOCK) INNER JOIN
                      Tagged_Page_Portlet AS TPP WITH (UPDLOCK) ON TPS.TaggedPageSectionID = TPP.TaggedPageSectionID INNER JOIN
                      Tagged_Portlet AS TP WITH (UPDLOCK) ON TPP.TaggedPortletID = TP.TaggedPortletID INNER JOIN
                      Tagged_Template AS TT WITH (UPDLOCK) ON TP.TaggedTemplateID = TT.TaggedTemplateID LEFT OUTER JOIN
                      Tagged_Page_Component_Column AS TPCC WITH (UPDLOCK) ON TT.ComponentCode = TPCC.ComponentCode AND
                      TPP.RangeColumn = TPCC.ColumnName
WHERE     (TPS.TaggedPageLayoutID = @TPLID)

  -- If TPPID is passed in, then we only care about this TaggedPagePortlet
  IF @TPPID <> 0
  BEGIN
    DELETE FROM #TempTPP WITH (UPDLOCK)
     WHERE TaggedPagePortletID <> @TPPID
  END
  
  -- Temporary table that will initially contain matched rows from Component_Interest_Categories table.
  -- This will be PER TaggedPagePortletID associated through ComponentCode
  CREATE TABLE #TempCIC
  (
   TaggedPagePortletID NUMERIC,
   UseOR CHAR(1),
   ComponentCode CHAR(2),
   ComponentID NUMERIC,
   InterestCategoryID NUMERIC

)

  -- For each TaggedPagePortletID, put component info into a temporary table which matched
  -- all the ICs passedin via @LstInterestCategoryID

  -- Putting comma at the end to make logic a bit easier when I use CHARINDEX()
  SET @TempList = @LstInterestCategoryID + ','
  SET @LastStringPtr = 1
  SET @StringPtr = CHARINDEX(',', @TempList, @LastStringPtr)

  -- If 0 is passed as @LstInterestCategoryID, this means there is no IC to match so insert all
  -- valid records into this temporary table.  
  -- Note: Did join to Tagged_Page_PortletIC and checked for "current" records to cut down on
  -- insert time.
  /* Added on 12/15/2003 */

  IF SUBSTRING(@TempList, @LastStringPtr, @StringPtr - @LastStringPtr) = 0
BEGIN
    INSERT INTO #TempCIC
           (TaggedPagePortletID, UseOR, ComponentCode, ComponentID, InterestCategoryID)
    SELECT TPP.TaggedPagePortletID, TPP.UseOR, CIC.ComponentCode, CIC.ComponentID, CIC.InterestCategoryID
      FROM #TempTPP TPP  WITH (UPDLOCK) LEFT OUTER JOIN Tagged_Page_PortletIC TPPIC WITH (UPDLOCK) ON TPP.TaggedPagePortletID = TPPIC.TaggedPagePortletID,
           Component_Interest_Category CIC WITH (UPDLOCK)
     WHERE TPP.ComponentCode = CIC.ComponentCode
       AND (CIC.InterestCategoryID = TPPIC.InterestCategoryID
            OR TPPIC.InterestCategoryID IS NULL)
       AND (CIC.ComponentCode = 'CM'
            AND EXISTS (SELECT TOP 1 1
                          FROM Content C WITH (UPDLOCK)
                         WHERE C.ContentID = CIC.ComponentID
                           AND C.WorkflowStatusCode = 'P'))

END
  -- Special operation to handle user defined interest categories
  ELSE IF @TPOperator = 'U'
  BEGIN
    INSERT INTO #TempCIC
           (TaggedPagePortletID, UseOR, ComponentCode, ComponentID, InterestCategoryID)
    SELECT TPP.TaggedPagePortletID, TPP.UseOR,
           CIC.ComponentCode, CIC.ComponentID, CIC.InterestCategoryID
      FROM #TempTPP TPP WITH (UPDLOCK), Component_Interest_Category CIC WITH (UPDLOCK)
     WHERE TPP.ComponentCode = CIC.ComponentCode
       AND CIC.InterestCategoryID IN (SELECT CIC2.InterestCategoryID
                                        FROM Contact_Interest_Category CIC2 WITH (UPDLOCK)
                                       WHERE CIC2.ContactID = @ContactID)

  END
  ELSE
  BEGIN
    IF @TPOperator = 'O'  
    BEGIN
      -- Stored Procedure does not allow something like XX IN (@LstInterestCategoryID)
      -- so do loop.
      INSERT INTO #TempCIC
             (TaggedPagePortletID, UseOR, ComponentCode, ComponentID, InterestCategoryID)
      SELECT TPP.TaggedPagePortletID, TPP.UseOR,
             CIC.ComponentCode, CIC.ComponentID, CIC.InterestCategoryID
        FROM #TempTPP TPP WITH (UPDLOCK), Component_Interest_Category CIC  WITH (UPDLOCK)
       WHERE TPP.ComponentCode = CIC.ComponentCode
         AND CIC.ComponentID IN (SELECT CIC2.ComponentID
                                   FROM Component_Interest_Category CIC2 WITH (UPDLOCK)
                                  WHERE CIC2.ComponentCode = CIC.ComponentCode
                                    AND CIC2.InterestCategoryID = SUBSTRING(@TempList, @LastStringPtr, @StringPtr - @LastStringPtr))
  
      SET @LastStringPtr = @StringPtr + 1
      SET @StringPtr = CHARINDEX(',', @TempList, @LastStringPtr)

      -- If more than one IC has been provided, loop through the rest of ICs to add
      WHILE @StringPtr > 0
      BEGIN
        INSERT INTO #TempCIC
               (TaggedPagePortletID, UseOR, ComponentCode, ComponentID, InterestCategoryID)
        SELECT TPP.TaggedPagePortletID, TPP.UseOR,
               CIC.ComponentCode, CIC.ComponentID, CIC.InterestCategoryID
          FROM #TempTPP TPP WITH (UPDLOCK), Component_Interest_Category CIC  WITH (UPDLOCK)
         WHERE TPP.ComponentCode = CIC.ComponentCode
           AND CIC.ComponentID IN (SELECT CIC2.ComponentID
                                     FROM Component_Interest_Category CIC2
                                    WHERE CIC2.ComponentCode = CIC.ComponentCode
                                      AND CIC2.InterestCategoryID = SUBSTRING(@TempList, @LastStringPtr, @StringPtr - @LastStringPtr))
           AND CIC.ComponentID NOT IN (SELECT T2.ComponentID
                                         FROM #TempCIC T2 WITH (UPDLOCK)
                                        WHERE T2.ComponentCode = CIC.ComponentCode
                                          AND T2.InterestCategoryID = SUBSTRING(@TempList, @LastStringPtr, @StringPtr - @LastStringPtr))
        SET @LastStringPtr = @StringPtr + 1
        SET @StringPtr = CHARINDEX(',', @TempList, @LastStringPtr)
      END
    END
    ELSE
    BEGIN
      INSERT INTO #TempCIC
             (TaggedPagePortletID, UseOR, ComponentCode, ComponentID, InterestCategoryID)
      SELECT TPP.TaggedPagePortletID, TPP.UseOR,
             CIC.ComponentCode, CIC.ComponentID, CIC.InterestCategoryID
        FROM #TempTPP TPP WITH (UPDLOCK), Component_Interest_Category CIC  WITH (UPDLOCK)
       WHERE TPP.ComponentCode = CIC.ComponentCode
         AND CIC.ComponentID IN (SELECT CIC2.ComponentID
                                   FROM Component_Interest_Category CIC2 WITH (UPDLOCK)
                                  WHERE CIC2.ComponentCode = CIC.ComponentCode
                                    AND CIC2.InterestCategoryID = SUBSTRING(@TempList, @LastStringPtr, @StringPtr - @LastStringPtr))
  
      SET @LastStringPtr = @StringPtr + 1
      SET @StringPtr = CHARINDEX(',', @TempList, @LastStringPtr)

      -- If more than one IC has been provided, loop through the rest of ICs to narrow the
      -- results which match all the ICs
      WHILE @StringPtr > 0
      BEGIN
        DELETE #TempCIC
         WHERE ComponentID NOT IN (SELECT CIC.ComponentID
                                     FROM Component_Interest_Category CIC WITH (UPDLOCK)
                                    WHERE CIC.ComponentCode = #TempCIC.ComponentCode
                                      AND CIC.InterestCategoryID = SUBSTRING(@TempList, @LastStringPtr, @StringPtr - @LastStringPtr))
        SET @LastStringPtr = @StringPtr + 1
        SET @StringPtr = CHARINDEX(',', @TempList, @LastStringPtr)
      END
    END

END


  -- Grab any important values passed into the script which can later be used to filter
  -- the result set.
  SET @UserDefinedDate1 = ''
  SET @TempList = @ExtraURLVar + '&'
  SET @LastStringPtr = 1
  SET @StringPtr = CHARINDEX('&', @TempList, @LastStringPtr)

  WHILE @StringPtr > 0
  BEGIN
    SET @TempVar = SUBSTRING(@TempList, @LastStringPtr, @StringPtr - @LastStringPtr)
    IF (CHARINDEX('=',@TempVar) != 0 AND UPPER(SUBSTRING(@TempVar,1,CHARINDEX('=',@TempVar)-1)) = 'USERDEFINEDDATE1')
      SET @UserDefinedDate1 = SUBSTRING(@TempVar,CHARINDEX('=',@TempVar)+1,LEN(@TempVar)-CHARINDEX('=',@TempVar))

    SET @LastStringPtr = @StringPtr + 1
    SET @StringPtr = CHARINDEX(',', @TempList, @LastStringPtr)
  END
  
  
  /*
  ** IMPORTANT!!!!
  **
  ** Now #TempCIC table contains only and unique results from component_interest_category
  ** for each TaggedPagePortletID that matched the list of ICs passed into this
  ** stored procedure via @LstInterestCategoryID
  **
  ** Next step is to match all ICs specfied for each TaggedPagePortlet. This is
  ** accomplished by first deleting ICs that are not part of the ICs of each
  ** TaggedPagePortlet which will result in only the ICs that matched will remain.
  ** So at the end of this delete, there can NOT be more ICs per TaggedPagePortlet
  ** in the #TempCIC than the number of ICs specified for each TaggedPagePortlet.
  **
  ** So the logic is if after the delete, there are same number of IC for each
  ** TaggedPagePortlet in #TempCIC table as ICs specified for each TaggedPagePortlet in
  ** TaggedPagePortletIC table, then the condition for matching all ICs is met.
  **
  ** The last step is to select unique values from #TempCIC and save it for SELECT
  ** logic.
  **
  ** Addition: IK Delete on #TempCIC will not occurr if no entry in TPPIC.
  */

  DELETE #TempCIC
   WHERE NOT Exists (SELECT *

                       FROM Tagged_Page_PortletIC TPPIC WITH (UPDLOCK)
                      WHERE TPPIC.InterestCategoryID = #TempCIC.InterestCategoryID
                        AND TPPIC.TaggedPagePortletID = #TempCIC.TaggedPagePortletID)
     AND EXISTS (SELECT *
                   FROM Tagged_Page_PortletIC TPPIC2 WITH (UPDLOCK)
                  WHERE TPPIC2.TaggedPagePortletID = #TempCIC.TaggedPagePortletID)
                        



  SELECT TaggedPagePortletID, ComponentID, ComponentCode
    INTO #TempCIC2
    FROM #TempCIC WITH (UPDLOCK)
   GROUP BY TaggedPagePortletID, ComponentID, ComponentCode
  HAVING Count(*) = (SELECT Count(*)
                       FROM Tagged_Page_PortletIC TPPIC WITH (UPDLOCK)
                      WHERE TPPIC.TaggedPagePortletID = #TempCIC.TaggedPagePortletID)


  DELETE #TempCIC
   WHERE NOT EXISTS (SELECT *
                       FROM #TempCIC2 T2 WITH (UPDLOCK)
                      WHERE T2.TaggedPagePortletID = #TempCIC.TaggedPagePortletID
                        AND T2.ComponentID = #TempCIC.ComponentID
                        AND T2.ComponentCode = #TempCIC.ComponentCode)
     AND UseOR = 'N'
     AND EXISTS (SELECT *
                   FROM Tagged_Page_PortletIC TPPIC2 WITH (UPDLOCK)
                  WHERE TPPIC2.TaggedPagePortletID = #TempCIC.TaggedPagePortletID)

  SELECT DISTINCT TaggedPagePortletID, ComponentCode, ComponentID
    INTO #Temp
    FROM #TempCIC WITH (UPDLOCK)



  -- Loop through each TaggedPagePortletID (component) in specified order to generate dynamic SQL to excute.
  -- Dynamic SQL is a must since we allow users to specify "Order By" columns

SET @DEBUG=GETDATE()

  DECLARE c_TPP CURSOR  FAST_FORWARD FOR
    SELECT TPP.TaggedSectionID,
           TPP.TaggedPagePortletID,
           TPP.ComponentCode,
           TPP.MaxRows,
           TPP.PrimarySort,
           TPP.PrimarySortAscDescInd,
           TPP.SecondarySort,
           TPP.SecondarySortAscDescInd,
           TPP.OrderNum,
           TPP.SQLNumber,
           TPP.RangeFrom,
           TPP.RangeTo,
           TPP.RangeColumn,
           TPP.ColumnType
      FROM #TempTPP TPP WITH (NOLOCK)
     ORDER BY TPP.TaggedSectionID, TPP.OrderNum

  SET @CurrentDate = CONVERT(VARCHAR(2),MONTH(CURRENT_TIMESTAMP))+'/'+CONVERT(VARCHAR(2),DAY(CURRENT_TIMESTAMP))+'/'+CONVERT(VARCHAR(4),Year(CURRENT_TIMESTAMP))
  OPEN c_TPP
  
  WHILE (1 = 1)
  BEGIN
    FETCH NEXT FROM c_TPP INTO
         @TaggedSectionID,
         @TaggedPagePortletID,
         @ComponentCode,
         @MaxRows,
         @PrimarySort,
         @PrimarySortAscDescInd,
         @SecondarySort,
         @SecondarySortAscDescInd,
         @OrderNum,
         @SQLNumber,
         @RangeFrom,
         @RangeTo,
         @RangeColumn,
         @ColumnType

    IF @@Fetch_Status <> 0
      BREAK

    SET @SQL = 'SELECT'
    IF (@TPPID = 0 AND @MaxRows IS NOT NULL)
      SET @SQL = @SQL + ' TOP ' + CONVERT(VARCHAR(10),@MaxRows)
    ELSE
      SET @MaxRows = 10000

    SET @TempStr1 = ''
    SET @TempStr2 = ''

    IF @RangeColumn IS NOT NULL
    BEGIN
      IF @ColumnType = 'Date'
      BEGIN
        IF @RangeFrom IS NOT NULL
        BEGIN
          IF ISDATE(@RangeFrom) = 1
          BEGIN
            SET @TempStr1 = ' AND ' + @RangeColumn + ' >= ''' + @RangeFrom + ''''
            SET @TempStr2 = ' AND ' + @RangeColumn + ' >= ''' + @RangeFrom + ''''
          END
          ELSE IF ISNUMERIC(@RangeFrom) = 1
          BEGIN
            SET @TempStr1 = ' AND ' + @RangeColumn + ' >= DATEADD(d,' + @RangeFrom + ',''' + @CurrentDate + ''')'
            SET @TempStr2 = ' AND ' + @RangeColumn + ' >= DATEADD(d,' + @RangeFrom + ',''' + @CurrentDate + ''')'
          END
        END
        IF @RangeTo IS NOT NULL
        BEGIN
          IF ISDATE(@RangeTo) = 1
          BEGIN
            SET @TempStr1 = @TempStr1 + ' AND ' + @RangeColumn + ' <= ''' + @RangeTo + ' 23:59:59'''
            SET @TempStr2 = @TempStr2 + ' AND ' + @RangeColumn + ' <= ''' + @RangeTo + ' 23:59:59'''
          END
          ELSE IF ISNUMERIC(@RangeTo) = 1
          BEGIN
            SET @TempStr1 = @TempStr1 + ' AND ' + @RangeColumn + ' <= DATEADD(d,' + @RangeTo + ',''' + @CurrentDate + ' 23:59:59'')'
            SET @TempStr2 = @TempStr2 + ' AND ' + @RangeColumn + ' <= DATEADD(d,' + @RangeTo + ',''' + @CurrentDate + ' 23:59:59'')'
          END
        END
      END
      ELSE IF @ColumnType = 'Numeric'
      BEGIN
        IF @RangeFrom IS NOT NULL AND ISNUMERIC(@RangeFrom) = 1
          BEGIN
            SET @TempStr1 = ' AND ' + @RangeColumn + ' >= ' + @RangeFrom
            SET @TempStr2 = ' AND ' + @RangeColumn + ' >= ' + @RangeFrom
          END
        IF @RangeTo IS NOT NULL AND ISNUMERIC(@RangeTo) = 1
          BEGIN
            SET @TempStr1 = @TempStr1 + ' AND ' + @RangeColumn + ' <= ' + @RangeTo
            SET @TempStr2 = @TempStr2 + ' AND ' + @RangeColumn + ' <= ' + @RangeTo
          END
      END
      ELSE
      BEGIN
        IF @RangeFrom IS NOT NULL
       BEGIN
            SET @TempStr1 = ' AND ' + @RangeColumn + ' >= ''' + @RangeFrom + ''''
            SET @TempStr2 = ' AND ' + @RangeColumn + ' >= ''' + @RangeFrom + ''''
          END
        IF @RangeTo IS NOT NULL
          BEGIN
            SET @TempStr1 = @TempStr1 + ' AND ' + @RangeColumn + ' <= ''' + @RangeTo + ''''
            SET @TempStr2 = @TempStr2 + ' AND ' + @RangeColumn + ' <= ''' + @RangeTo + ''''
          END
      END
    END
    IF @ComponentCode = 'CM'
    BEGIN
      -- Used to filter out any restricted content if the site is set to hide member only data from
      -- registered users.
      IF @HideMemberOnlyFlag = 1
        SET @TempVar = '(''N'','''')'
      ELSE
        SET @TempVar = '(''N'',''Y'','''')'

      -- If UserDefinedDate1 is provided, setup the temporary variables to plug into
      -- the SQL construct.
      IF @UserDefinedDate1 != ''
      BEGIN
        SET @TempStr1 = @TempStr1 + ' AND C.UserDefinedDate1 = ''' + @UserDefinedDate1 + ''''
        SET @TempStr2 = @TempStr2 + ' AND C2.UserDefinedDate1 = ''' + @UserDefinedDate1 + ''''
      END

    SET @tmpSQL='
    DECLARE @Flag INT
    SELECT @Flag= (CASE
     WHEN Count(*) > ' + CONVERT(VARCHAR(10),@MaxRows) + ' THEN 1
     ELSE 0 END)
    FROM #Temp T2 WITH (UPDLOCK, FASTFIRSTROW)
    JOIN vContent_TemplatePath C2 on T2.ComponentID = C2.ContentID
    WHERE
    ISNULL(C2.MembersOnlyFlag,''N'') IN ' + @TempVar + '
    AND T2.TaggedPagePortletID = ' + CONVERT(VARCHAR(10),@TaggedPagePortletID) + @TempStr2 + '  '


        SET @SQL = @SQL + ' ' + CONVERT(VARCHAR(10),@TaggedSectionID) + ' TaggedSectionID, '
                     + CONVERT(VARCHAR(10),@TaggedPagePortletID) + ' TaggedPagePortletID,  
                       C.*, C.TemplatePath, @Flag  as MoreFlag
                         FROM #Temp T WITH (UPDLOCK, FASTFIRSTROW)
                Join vContent_TemplatePath C on T.ComponentID = C.ContentID
                        WHERE
                             ISNULL(C.MembersOnlyFlag,''N'') IN ' + @TempVar + '
                              AND
                T.TaggedPagePortletID = ' + CONVERT(VARCHAR(10),@TaggedPagePortletID)
                          + @TempStr1
    END

    ELSE IF @ComponentCode = 'CC'
    BEGIN
      SET @SQL = @SQL + ' ' + CONVERT(VARCHAR(10),@TaggedSectionID) + ' TaggedSectionID, '
                 + CONVERT(VARCHAR(10),@TaggedPagePortletID) + ' TaggedPagePortletID,
                   0 MoreFlag'  
    END
    
    IF @PrimarySort IS NOT NULL
    BEGIN
      SET @SQL = @SQL + ' ORDER BY ' + @PrimarySort
      IF @PrimarySortAscDescInd = 'D'
        SET @SQL = @SQL + ' DESC'
      ELSE
        SET @SQL = @SQL + ' ASC'
      IF @SecondarySort IS NOT NULL
      BEGIN
        SET  @SQL = @SQL + ', ' + @SecondarySort
        IF @SecondarySortAscDescInd = 'D'
          SET @SQL = @SQL + ' DESC'
        ELSE
          SET @SQL = @SQL + ' ASC'
      END
    END

--PRINT 'Query is: ' + @SQL
--Print ''
SEt @DEBUG=Getdate()
-- D. Eliseev 03/01/2005 - Combine Final SQL string as SQLBatch String and Quert itself.
set @SQLString=@tmpSQL + @SQL

--PRINT 'Query is: ' + @SQL
--Print ''

EXECUTE sp_executesql @SQLString

--PRINT 'Dynamic SQL Stime (ms) : ' + cast(datediff(ms,@DEBUG,Getdate()) as varchar(10))
  END

  CLOSE c_TPP
  DEALLOCATE c_TPP

END

GO
GRANT EXECUTE ON  [dbo].[amsp_GetTaggedPage] TO [IMIS]
GO